# -*- coding: utf-8 -*-

from odoo import models, fields, api
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
import psycopg2
import logging

db_name = "odoo"
db_user = "gc740"
db_pass = "123456"
db_ip = "127.0.0.1"
error_log = "____"
# 定义日志输出格式
logging.basicConfig(level=logging.ERROR,
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                    datefmt='%Y-%m-%d %H:%M:%S',
                    filename=error_log,
                    filemode='a')


def writeDb(sql, data):
    """
    连接mysql数据库（写），并进行写的操作，如果连接失败，会把错误写入日志中，并返回false，如果sql执行失败，也会把错误写入日志中，并返回false，如果所有执行正常，则返回true
    """
    try:
        conn = psycopg2.connect(database=db_name, user=db_user, password=db_pass, host=db_ip, port=5432)
        cursor = conn.cursor()
        print '数据库连接成功'
    except Exception, e:
        print e
        logging.error('数据库连接失败:%s' % e)
        return False
    print 'test write db'
    try:
        sql = sql % data
        print 'sql'
        print sql
        cursor.execute(sql)
        conn.commit()  # 提交事务
        print 'test commit 2'
    except Exception, e:
        print 'error1'
        print e
        conn.rollback()  # 如果出错，则事务回滚
        logging.error('数据写入失败:%s' % e)
        return False
    finally:
        cursor.close()
        conn.close()
    print '数据库写数据成功'
    return True


def readDb(sql):
    """
    连接mysql数据库（从），并进行数据查询，如果连接失败，会把错误写入日志中，并返回false，如果sql执行失败，也会把错误写入日志中，并返回false，如果所有执行正常，则返回查询到的数据，这个数据是经过转换的，转成字典格式，方便模板调用，其中字典的key是数据表里的字段名
    """
    try:
        conn = psycopg2.connect(database=db_name, user=db_user, password=db_pass, host=db_ip, port=5432)
        cursor = conn.cursor()
    except Exception, e:
        print e
        logging.error('数据库连接失败:%s' % e)
        return False
    try:
        cursor.execute(sql)
        data = [dict((cursor.description[i][0], value) for i, value in enumerate(row)) for row in
                cursor.fetchall()]  # 转换数据，字典格式
    except Exception, e:
        logging.error('数据执行失败:%s' % e)
        return False
    finally:
        cursor.close()
        conn.close()
    return data


# sql = "SELECT public.sale_order.id AS 销售订单, " \
#       "public.res_partner.display_name AS 销售员 " \
#       "FROM public.sale_order " \
#       "INNER JOIN public.res_users ON public.sale_order.user_id = public.res_users.id " \
#       "INNER JOIN public.res_partner ON public.res_users.partner_id = public.res_partner.id " \
#       "WHERE public.sale_order.invoice_status = 'invoiced'"

sql = "SELECT public.sale_order.id AS 销售订单, " \
      "public.sale_order.confirmation_date AS 确认日期, " \
      "public.sale_order.subscription_period AS 认购期限, " \
      "public.sale_order.amount_untaxed AS 未含税金额, " \
      "public.sale_order.amount_total AS 总计, " \
      "public.sale_order.invoice_status AS 发票状态, " \
      "public.res_partner.name AS 客户, " \
      "public.product_template.name AS 产品名,	" \
      "public.product_template.list_price AS 标价,	" \
      "public.product_template.type AS 产品类型, " \
      "public.product_template.is_financial AS 金融产品, " \
      "public.product_template.annual_interest AS 年利率, " \
      "public.product_template.sale_bonus AS 产品提成, " \
      "public.product_template.type_financial AS 金融产品类型, " \
      "public.sale_order_line.product_uom_qty AS 订购数量, " \
      "public.sale_order_line.qty_invoiced AS 已开发票数量, " \
      "public.sale_order_line.price_unit AS 单价, " \
      "public.sale_order_line.name AS 说明, " \
      "public.account_invoice.number AS 号码, " \
      "public.account_invoice.date_invoice AS 开票日期, " \
      "public.account_invoice.origin AS 源单据, " \
      "public.account_invoice.date_due AS 截止日期, " \
      "public.account_invoice.residual AS 截止金额 " \
      "FROM public.sale_order " \
      "INNER JOIN public.sale_order_line ON public.sale_order_line.order_id = public.sale_order.id " \
      "INNER JOIN public.res_partner ON public.sale_order.partner_id = public.res_partner.id " \
      "INNER JOIN public.product_product ON public.sale_order_line.product_id = public.product_product.id " \
      "INNER JOIN public.product_template ON public.product_product.product_tmpl_id = public.product_template.id " \
      "INNER JOIN public.res_users ON public.sale_order.user_id = public.res_users.id " \
      "INNER JOIN public.account_invoice ON public.account_invoice.partner_id = public.res_partner.id " \
      "WHERE public.sale_order.invoice_status = 'invoiced'"

data = readDb(sql)

class Temporary(models.Model):
    _name = 'temporary.data'

    name = fields.Char()
    sale_order_id = fields.Integer("销售订单ID")
    partner = fields.Char("客户")
    saleman = fields.Char("销售员")
    crm_team = fields.Char("销售团队")

    # sale_order
    confirmation_date = fields.Datetime("确认日期")  # 1
    subscription_period = fields.Integer("认购期限")  # 1
    amount_untaxed = fields.Float("未含税金额")
    amount_total = fields.Float("总计")  # 1
    invoice_status = fields.Char("发票状态")

    # product_template
    product_name = fields.Char("产品名")  # 1
    product_type = fields.Char("产品类型")
    list_price = fields.Float("标价")
    is_financial = fields.Boolean("金融产品")
    annual_interest = fields.Float("年利率")  # 1
    sale_bonus = fields.Float("产品提成")
    type_financial = fields.Integer("金融产品类型")  # 1

    # sale_order_line
    product_uom_qty = fields.Float("订购数量")
    qty_invoiced = fields.Float("已开发票数量")
    price_unit = fields.Float("单价")
    product_description = fields.Char("产品说明")  # 1

    # account_invoice
    invoice_number = fields.Char("发票号码")  # 1
    date_invoice = fields.Date("开票日期")  # 1
    origin = fields.Char("源单据")
    date_due = fields.Date("截止日期")
    residual = fields.Float("截止金额")

    # 新增还款四个字段
    repayment_times_total = fields.Integer("还款总期数")
    repayment_time = fields.Datetime("第几期的还款")
    repayment_amount = fields.Float("本期还款数额")
    repayment_state = fields.Boolean("还款状态", default=False)

    def calculate(self):
        print 'this is cal'
        for i in range(len(data)):
            if int(data[i]["金融产品类型"]) == 2:
                confirm_date = datetime.strptime(data[i]["确认日期"], "%Y-%m-%d %H:%M:%S")  # 订单确认日期
                period = int(data[i]["认购期限"]) / 3  # 还款次数
                print "confirm_date is %s" % confirm_date
                print "period is %s" % period
                interest = data[i]["总计"] * data[i]["年利率"] / 12 * 3  # 利息
                principal_interest = data[i]["总计"] + interest  # 本息
                count = 1
                while count <= period:
                    repay_date = confirm_date + relativedelta(months=+3) + relativedelta(days=-1)  # 还款日期
                    if count == 1:
                        print 'if 1'
                        # print "repay_date is %s" % repay_date
                        # print "interest is %s" % interest
                        sql = "insert into public.temporary_data(repayment_times_total,repayment_time, repayment_amount) values(%s,%s,%s)"
                        data1 = (period, repay_date, interest)
                        writeDb(sql, data1)
                    elif count == period:
                        print 'ytest2'
                        repay_date = repay_date + relativedelta(months=+3)
                        # print "repay_date is %s" % repay_date
                        # print "principal_interest is %s" % principal_interest
                        sql = "insert into public.temporary_data(repayment_times_total,repayment_time, repayment_amount) values(%s,%s,%s)"
                        data1 = (period, repay_date, principal_interest)
                        writeDb(sql, data1)
                    else:
                        repay_date = repay_date + relativedelta(months=+3)
                        # print "repay_date is %s" % repay_date
                        # print "interest is %s" % interest
                        print 'test1'
                        sql = "insert into public.temporary_data(repayment_times_total,repayment_time, repayment_amount) values(%s,%s,%s)"
                        data1 = (period, repay_date, interest)
                        print 'test2'
                        print sql,data1
                        writeDb(sql, data1)
                    count += 1
        # return True




data = readDb(sql)
# for i in range(len(data)):
#     if int(data[i]["金融产品类型"]) == 2:
#         confirm_date = datetime.strptime(data[i]["确认日期"], "%Y-%m-%d %H:%M:%S")  # 订单确认日期
#         period = int(data[i]["认购期限"]) / 3  # 还款次数
#         print "confirm_date is %s" % confirm_date
#         print "period is %s" % period
#         interest = data[i]["总计"] * data[i]["年利率"] / 12 * 3  # 利息
#         principal_interest = data[i]["总计"] + interest  # 本息
#         count = 1
#         while count <= period:
#             repay_date = confirm_date + relativedelta(months=+3) + relativedelta(days=-1)  # 还款日期
#             if count == 1:
#                 # print "repay_date is %s" % repay_date
#                 # print "interest is %s" % interest
#                 sql = "insert into public.temporary_data(repayment_time, repayment_amount) values(%s,%s)"
#                 data1=(repay_date,interest)
#                 status = writeDb(sql, data1)
#             elif count == period:
#                 repay_date = repay_date + relativedelta(months=+3)
#                 # print "repay_date is %s" % repay_date
#                 # print "principal_interest is %s" % principal_interest
#                 sql = "insert into public.temporary_data(repayment_time, repayment_amount) values(%s,%s)"
#                 data1 = (repay_date, principal_interest)
#                 status = writeDb(sql, data1)
#             else:
#                 repay_date = repay_date + relativedelta(months=+3)
#                 # print "repay_date is %s" % repay_date
#                 # print "interest is %s" % interest
#                 sql = "insert into public.temporary_data(repayment_time, repayment_amount) values(%s,%s)"
#                 data1 = (repay_date, interest)
#                 status = writeDb(sql, data1)
#             count += 1
#         for i in range(len(data)):
#             if int(data[i]["金融产品类型"]) == 2:
#                 confirm_date = datetime.strptime(data[i]["确认日期"], "%Y-%m-%d %H:%M:%S")  # 订单确认日期
#                 period = int(data[i]["认购期限"]) / 3  # 还款次数
#                 print "confirm_date is %s" % confirm_date
#                 print "period is %s" % period
#                 interest = data[i]["总计"] * data[i]["年利率"] / 12 * 3  # 利息
#                 principal_interest = data[i]["总计"] + interest  # 本息
#                 count = 1
#                 while count <= period:
#                     repay_date = confirm_date + relativedelta(months=+3) + relativedelta(days=-1)  # 还款日期
#                     if count == 1:
#                         print 'if 1'
#                         # print "repay_date is %s" % repay_date
#                         # print "interest is %s" % interest
#                         sql = "insert into public.temporary_data(repayment_times_total,repayment_time, repayment_amount) values(%s,%s,%s)"
#                         data1 = (period, repay_date, interest)
#                         writeDb(sql, data1)
#                     elif count == period:
#                         print 'ytest2'
#                         repay_date = repay_date + relativedelta(months=+3)
#                         # print "repay_date is %s" % repay_date
#                         # print "principal_interest is %s" % principal_interest
#                         sql = "insert into public.temporary_data(repayment_times_total,repayment_time, repayment_amount) values(%s,%s,%s)"
#                         data1 = (period, repay_date, principal_interest)
#                         writeDb(sql, data1)
#                     else:
#                         repay_date = repay_date + relativedelta(months=+3)
#                         # print "repay_date is %s" % repay_date
#                         # print "interest is %s" % interest
#                         print 'test1'
#                         sql = "insert into public.temporary_data(repayment_times_total,repayment_time, repayment_amount) values(%s,%s,%s)"
#                         data1 = (period, repay_date, interest)
#                         print 'test2'
#                         print sql,data1
#                         writeDb(sql, data1)
#                     count += 1
#         # return True

print 'this is cal'
for i in range(len(data)):
    if int(data[i]["金融产品类型"]) == 2:
        confirm_date = datetime.strptime(data[i]["确认日期"], "%Y-%m-%d %H:%M:%S")  # 订单确认日期
        period = int(data[i]["认购期限"]) / 3  # 还款次数
        print "confirm_date is %s" % confirm_date
        print "period is %s" % period
        interest = data[i]["总计"] * data[i]["年利率"] / 12 * 3  # 利息
        principal_interest = data[i]["总计"] + interest  # 本息
        count = 1
        while count <= period:
            repay_date = confirm_date + relativedelta(months=+3) + relativedelta(days=-1)  # 还款日期
            if count == 1:
                print 'if 1'
                # print "repay_date is %s" % repay_date
                # print "interest is %s" % interest
                sql = "insert into public.temporary_data(repayment_times_total,repayment_time, repayment_amount) values(%s,%s,%s)"
                data1 = (period, repay_date, interest)
                writeDb(sql, data1)
            elif count == period:
                print 'ytest2'
                repay_date = repay_date + relativedelta(months=+3)
                # print "repay_date is %s" % repay_date
                # print "principal_interest is %s" % principal_interest
                sql = "insert into public.temporary_data(repayment_times_total,repayment_time, repayment_amount) values(%s,%s,%s)"
                data1 = (period, repay_date, principal_interest)
                writeDb(sql, data1)
            else:
                repay_date = repay_date + relativedelta(months=+3)
                # print "repay_date is %s" % repay_date
                # print "interest is %s" % interest
                print 'test1'
                sql = "insert into public.temporary_data(repayment_times_total,repayment_time, repayment_amount) values(%s,%s,%s)"
                data1 = (period, repay_date, interest)
                print 'test2'
                print sql,data1
                writeDb(sql, data1)
            count += 1
# return True